package database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBmanager {

	private static DBmanager uniqueInstance=null;

	private Connection con = null;
	private final String driver = "org.apache.derby.jdbc.EmbeddedDriver" ;
//	private final String dbname = "jdbc:derby:C:/VeilingsiteDB/database;create=true" ;
	private String dbname;


	private DBmanager(){
		
	}

	public void setInit(String dbname){
		this.dbname = dbname;
		if(!dbExists(dbname)){
		}
		createDBTables();
	}

	public void createDBTables(){
		String createTableGebruikers = "CREATE TABLE Gebruikers(" +
				"	id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
				"	voornaam VARCHAR(255) NOT NULL," +
				"	achternaam VARCHAR(255) NOT NULL," +
				"	inlognaam VARCHAR(255) NOT NULL," +
				"	wachtwoord VARCHAR(255) NOT NULL," +
				"	UNIQUE(inlognaam))";
		String createTableVeilingen = "CREATE TABLE Veilingen(" +
				"	id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
				"	titel VARCHAR(255) NOT NULL," +
				"	foto VARCHAR(255)," +
				"	prijs DOUBLE," +
				"	eindtijd TIMESTAMP NOT NULL," +
				"	verkoperid INT NOT NULL," +
				"	omschrijving LONG VARCHAR DEFAULT 'Geen omschrijving opgegeven.'," +
				"	FOREIGN KEY (verkoperid) REFERENCES Gebruikers(id))";
		String createTableBiedingen = "CREATE TABLE Biedingen(" +
				"	biederid INT NOT NULL," +
				"	veilingid INT NOT NULL," +
				"	prijs DOUBLE," +
				"	datum BIGINT," +
				"	FOREIGN KEY (biederid) REFERENCES Gebruikers(id)," +
				"	FOREIGN KEY (veilingid) REFERENCES Veilingen(id))";
		try{
			Connection con = getConnection();
			Statement s = con.createStatement();
			
			DatabaseMetaData dbmd = con.getMetaData();
			ResultSet rs = dbmd.getTables(null, "APP", "GEBRUIKERS", null);
			if(!rs.next())
			{
				s.execute(createTableGebruikers);
			}
			rs = dbmd.getTables(null, "APP", "VEILINGEN", null);
			if(!rs.next())
			{
				s.execute(createTableVeilingen);
			}
			rs = dbmd.getTables(null, "APP", "BIEDINGEN", null);
			if(!rs.next())
			{
				s.execute(createTableBiedingen);
			}
		} catch(SQLException e){
			e.printStackTrace();
		}
	}

	public static synchronized DBmanager getInstance() {
		if (uniqueInstance == null) {
			uniqueInstance = new DBmanager();
		}
		return uniqueInstance;
	}

	private boolean dbExists(String dataSource) {
		boolean exists = false ;
		try{
			Class.forName(driver).newInstance();
		}catch(Exception e ) {
			System.out.println("classpath");
		}
		try{
			con = DriverManager.getConnection(dataSource);

			exists = true ;
		} catch(Exception e){
			System.out.println("cannot connect to database");
			// Do nothing, as DB does not (yet) exist
		}
		return(exists) ;
	}

	public void close() {
		try {
			con = DriverManager.getConnection(dbname + ";shutdown=true") ;
		}catch(SQLException se) {
			; // Do Nothing. System has shut down.
			con = null ;
		}
	}

	public Connection getConnection() {
		return con;
	}
}
